package sc.tool.sqlgen.branchinfo;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import sc.prototype.common.ConsoleUtil;

public class XlsxUtil implements ConsoleUtil {

    public static List<String[]> loadItems(String filePath, String sheetName) {
        return loadItems(filePath, sheetName, 0);
    }

    public static List<String[]> loadItems(String filePath, String sheetName, int startRowIndex) {
        return loadItems(filePath, sheetName, startRowIndex, Integer.MAX_VALUE);
    }

    public static List<String[]> loadItems(String filePath, int beginRowIndex, String... sheetNames) {
        List<String[]> ret = new ArrayList<String[]>();
        Workbook workbook = getWorkbook(filePath);
        for (String sheetName : sheetNames) {
            loadItems(ret, workbook, sheetName, beginRowIndex, Integer.MAX_VALUE);
        }
        return ret;
    }

    /**
     * 获取文件内容
     */
    public static List<String[]> loadItems(String filePath, String sheetName, int beginRowIndex, int endRowIndex) {
        List<String[]> ret = new ArrayList<String[]>();
        Workbook workbook = getWorkbook(filePath);
        loadItems(ret, workbook, sheetName, beginRowIndex, endRowIndex);
        return ret;
    }

    public static void loadItems(List<String[]> list, Workbook workbook, String sheetName, int beginRowIndex,
            int endRowIndex) {
        Row curRow = null;
        try {
            Sheet sheet = workbook.getSheet(sheetName);
            for (Iterator<Row> rit = sheet.rowIterator(); rit.hasNext();) {
                Row row = rit.next();
                if (beginRowIndex > 0) {
                    beginRowIndex--;
                    continue;
                }
                if (row.getRowNum() >= endRowIndex) {
                    break;
                }
                curRow = row;
                list.add(toStringArray(row));
            }
        } catch (Exception e) {
            String err = String.format("Error Sheet(%s), Row(%s)", sheetName, curRow);
            throw new IllegalArgumentException(err, e);
        }
    }

    public static Workbook getWorkbook(String filePath) {
        InputStream inp;
        try {
            Console.info("XSSFWorkbook初始化：{}", filePath);
            inp = new FileInputStream(filePath);
            Workbook wb = new XSSFWorkbook(inp);
            Console.info("XSSFWorkbook初始化完成");
            return wb;
        } catch (Exception e) {
            Console.error("", e);
            throw new RuntimeException(e);
        }
    }

    public static String[] toStringArray(Row row) {
        List<String> list = new ArrayList<String>();
        for (Iterator<Cell> cit = row.cellIterator(); cit.hasNext();) {
            Cell cell = cit.next();
            list.add(toString(cell));
        }
        return list.toArray(new String[0]);
    }

    public static String toString(Cell cell) {
        String ret = null;
        switch (cell.getCellType()) {
        case Cell.CELL_TYPE_STRING:
            ret = cell.getRichStringCellValue().getString();
            break;
        case Cell.CELL_TYPE_NUMERIC:
            ret = Integer.toString((int) cell.getNumericCellValue());
            break;
        case Cell.CELL_TYPE_FORMULA:
            int formulaResultType = cell.getCachedFormulaResultType();
            if (formulaResultType == Cell.CELL_TYPE_ERROR) {
                ret = "";
            } else if (formulaResultType == Cell.CELL_TYPE_BOOLEAN) {
                ret = Boolean.toString(cell.getBooleanCellValue());
            } else {
                ret = cell.getStringCellValue();
            }
            break;
        case Cell.CELL_TYPE_ERROR:
            ret = "";
            break;
        default:
            ret = cell.getStringCellValue();
        }
        return ret;
    }

    public static void writeFile(String filePath, List<CreateSheet> createSheets) {
        try {
            XSSFWorkbook wb = new XSSFWorkbook();
            for (CreateSheet createSheet : createSheets) {
                Sheet sheet = wb.createSheet(createSheet.getSheetName());
                int rowCount = 0;
                for (String[] dataRow : createSheet.getDataRowList()) {
                    Row row = sheet.createRow(rowCount++);
                    for (int i = 0; i != dataRow.length; i++) {
                        row.createCell(i, Cell.CELL_TYPE_STRING).setCellValue(dataRow[i]);
                    }
                }
            }
            wb.write(new FileOutputStream(filePath));
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    public static void writeFile(String filePath, Workbook workBook) {
        try {
            workBook.write(new FileOutputStream(filePath));
        } catch (Exception e) {
            throw new RuntimeException("输出文件异常", e);
        }
    }

    public static void main(String[] args) {
        writeFile("D:/WeibopayMemo/出款服务FOS/branh_info_new.xlsx", Arrays.asList(new CreateSheet("export2", Arrays
                .asList(new String[] { "123", "abc", "诶比谁" }, new String[] { "123", "abc", "诶比谁2" }))));
    }
}
